package com.fenrirtec.aepp.common.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.fenrirtec.aepp.common.condition.MemberCondition;
import com.fenrirtec.aepp.common.dao.TenderQuoteDao;
import com.fenrirtec.aepp.common.model.TenderQuoteList;
import com.fenrirtec.core.database.DatabaseSessionManager;
import com.fenrirtec.core.database.ResultSetMapper;
import com.fenrirtec.core.exception.DatabaseException;

public class TenderQuoteDao_JDBCImpl implements TenderQuoteDao{
	private static final Logger logger = LoggerFactory.getLogger(TenderQuoteDao_JDBCImpl.class);
    @Override
    public List<TenderQuoteList> searchByCnd(MemberCondition condition ) {
        if (logger.isInfoEnabled()) {
            logger.info("[TenderQuoteDao_JDBCImpl#searchByCnd] start.");
        }

        PreparedStatement statement = null;
        
        try {
            
            StringBuilder sql = new StringBuilder()
            .append("select \n")
            .append("  tte.tender_id, \n")
            .append("tte.member_login_name, \n")
            .append("tte.quote_date ,\n")
            .append("tte.total_price ,\n")
            .append("tte.bid_flag ,\n")
            .append("tte.comment ,\n")
            .append("vti.tender_title ,\n")
            .append("vti.enroll_start_date ,\n")
            .append("vti.enroll_end_date ,\n")
            .append("vti.tender_end_date ,\n")
            .append("vti.publish_result_date ,\n")
            .append("vti.content_description ,\n")
            .append("vti.vendor_requirement ,\n")
            .append("vti.tender_pattern ,\n")
            .append("vti.tender_template ,\n")
            .append("vti.contact_name ,\n")
            .append("vti.contact_tel ,\n")
            .append("vti.tender_pattern_name ,\n")
            .append("vti.status_name ,\n")
            .append("vti.status ,\n")
            .append("vti.enroll_count ,\n")
            .append("vti.finalist_count ,\n")
            .append("vti.quote_count \n ,")
            .append("vti.tender_no \n")
            .append("from \n")
            .append("  t_tender_quote tte  \n")
            .append("join \n")
            .append("v_tender_information vti  \n")
            .append("on  \n")
            .append("(vti.tender_id =tte.tender_id) \n") 
            .append("where \n")
            .append("  vti.delete_flag = false \n");
            if (condition.getTenderId() != null) {
                sql.append("and \n");
                sql.append("  tte.tender_id  = ? \n");
            }
            if (StringUtils.isNotEmpty(condition.getMemberLoginName())) {
                sql.append("and \n");
                sql.append("  tte.member_login_name= ? \n");
            }
            sql.append("order by \n");
            sql.append("  vti.tender_id desc \n");
            if (logger.isTraceEnabled()) {
                logger.trace("[TenderQuoteDao_JDBCImpl#searchByCnd] sql={}", sql);
            }
            Connection connection = DatabaseSessionManager.getInstance().getSession();
            statement = connection.prepareStatement(sql.toString());
            if (condition != null) {
                int index = 0;
                if (condition.getTenderId() != null){
                    statement.setInt(++index, condition.getTenderId());
                }
                if (StringUtils.isNotEmpty(condition.getMemberLoginName())) {
                    statement.setString(++index, condition.getMemberLoginName());
                }
            }

            ResultSet resultSet = statement.executeQuery();
            ResultSetMapper<TenderQuoteList> resultSetMapper = new ResultSetMapper<TenderQuoteList>();
            List<TenderQuoteList> tenderQuoteList = resultSetMapper.mapResultSetToList(resultSet, TenderQuoteList.class);
            
            return tenderQuoteList;
            
            
        } catch (SQLException e) {
            if (logger.isTraceEnabled()) {
                logger.trace("[TenderQuoteDao_JDBCImpl#searchByCnd] sql error occurred.", e);
            }
            throw new DatabaseException("database error occurred.", e);
        } finally {
            if (logger.isInfoEnabled()) {
                logger.info("[TenderQuoteDao_JDBCImpl#searchByCnd] finish.");
            }
            if (statement != null) { try { statement.close(); } catch (SQLException e) {} }
        }
    }
}
